package com.admin.dao.impl;

import com.admin.dao.IAdmindao;
import com.admin.entity.Admin;
import com.admin.util.DBUtil;
import com.user.entity.BookInfo;
import com.user.entity.School;
import com.user.entity.Users;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AdminDaoImpl implements IAdmindao {
    @Override
    public List<Users> QueryUserList(int pageSize, int currentPage) {
        List<Users> usersList = new ArrayList<>();
        ResultSet resultSet = null;
        String sql = "select * from (select rownum r,t.* from(select id,username,password,school.schoolid,schoolname  from users,school where school.schoolid = users.schoolid ORDER BY ID) t WHERE ROWNUM<=?)WHERE r>=?";
        Object[] params = {pageSize * currentPage, pageSize * currentPage - pageSize + 1};
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                int schoolid = resultSet.getInt("schoolid");
                String schoolname = resultSet.getString("schoolname");
                usersList.add(new Users(id, username, password, schoolid, schoolname));

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return usersList;
    }

    @Override
    public int getUsersTotalCount() {
        int totalCount = 0;
        String sql = "select count(*) from Users";
        try {
            totalCount = com.user.util.DBUtil.getTotalCount(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return totalCount;
    }

    @Override
    public boolean adminLogin(Admin admin) {
        boolean result = false;
        ResultSet resultSet = null;

        String sql = "Select count(*) from admin where adminname =? and adminpassword =?";
        Object[] params = {admin.getAdminname(), admin.getAdminpassword()};
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            if (resultSet.next()) {
                int a = resultSet.getInt(1);
                if (a > 0)
                    result = true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;

    }

    @Override
    public boolean deleteUsers(Users users) {
        boolean result = false;
        String sql = "delete from users where id=?";
        Object[] params = {users.getId()};
        try {
            result = DBUtil.executeUpdate(sql, params);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public Users getUserAllmsg(int userid) {
        Users users = null;
        ResultSet resultSet = null;
        String sql = "select id,username,password,schoolname from users,school where id =? and USERS.SCHOOLID=SCHOOL.SCHOOLID";
        Object[] params = {userid};
        try {

            resultSet = DBUtil.executeQuery(sql, params);
            if (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String schoolname = resultSet.getString("schoolname");
                users = new Users(id, username, password, schoolname);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return users;
    }

    @Override
    public boolean IsExistSchoolname(String schoolname) {
        boolean result = false;
        ResultSet resultSet = null;
        String sql = "select count(*) from school where schoolname =?";
        Object[] params = {schoolname};
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            if (resultSet.next()) {
                int anInt = resultSet.getInt(1);
                if (anInt > 0) {
                    result = true;
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return result;
    }

    @Override
    public boolean updateUserInfo(Users users, int id) {
        boolean result = false;
        String sql = "update users set username =?,password =? ,schoolid =? where id =?";
        Object[] params = {users.getUsername(), users.getPassword(), users.getShoolid(), id};
        try {
            result = DBUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public int getSchoolid(String schoolname) {
        ResultSet resultSet = null;
        int schoolid = 0;
        String sql = "select schoolid from school where schoolname=?";
        Object[] params = {schoolname};
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            if (resultSet.next()) {
                schoolid = resultSet.getInt("schoolid");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return schoolid;
    }

    @Override
    public boolean addBooks(BookInfo bookInfo) {
        boolean result = false;
        String sql = "insert into bookinfo values(seq_bookid.nextval,?,?,?,?)";
        Object[] params = {bookInfo.getBookname(), bookInfo.getAuthor(), bookInfo.getCategory(), bookInfo.getNumber()};
        try {
            result = DBUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public BookInfo getBookInfo(int id) {
        ResultSet resultSet = null;
        BookInfo bookInfo = null;
        String sql = "select * from bookinfo where bookid =?";
        Object[] params = {id};
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            if (resultSet.next()) {
                int bookid = resultSet.getInt("bookid");
                String bookname = resultSet.getString("bookname");
                String author = resultSet.getString("author");
                String category = resultSet.getString("category");
                int number = resultSet.getInt("number");
                bookInfo = new BookInfo(bookid, bookname, author, category, number);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);
        return bookInfo;
    }

    @Override
    public boolean updateBookNumber(int bookid, int number) {
        boolean result = false;
        String sql = "UPDATE BOOKINFO set BOOKINFO.\"number\"=? WHERE BOOKID =?";

        Object[] params = {number, bookid};
        try {
            result = DBUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public List<School> getSchoolInfo() {
        ResultSet resultSet = null;
        List<School> schoolList = new ArrayList<>();
        String sql = "select * from school";
        Object[] params = null;
        try {
            resultSet = DBUtil.executeQuery(sql, params);
            while (resultSet.next()) {
                int schoolid = resultSet.getInt("schoolid");
                String schoolname = resultSet.getString("schoolname");
                schoolList.add(new School(schoolid, schoolname));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DBUtil.closeAll(resultSet, DBUtil.preparedStatement, DBUtil.connection);

        return schoolList;
    }

    @Override
    public boolean addSchool(String schoolname) {
        boolean result = false;
        String sql = "insert into school (schoolid,schoolname) values(seq_schoolid.nextval,?)";

        Object[] params = {schoolname};
        try {
            result = DBUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }


    @Override
    public boolean deleteSchool(int schoolid) {
        boolean result = false;
        String sql = "delete from school where schoolid =?";

        Object[] params = {schoolid};
        try {
            result = DBUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

}
